﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;
using SIPO.Library.DomainObject.Transaction;
using SIPO.Library.DomainObject.Administration.Catalog;

namespace SIPO.Library.DataAccess.Report
{
    public class rptRecBillingRepo
    {
        private string connectionString;

        public rptRecBillingRepo(string connectionString)
        {
            this.connectionString = connectionString;
        }

        public List<recBilling> getReportBilling(DateTime periodeAwal, DateTime periodeAkhir, DateTime dueDateAwal, DateTime dueDateAkhir, short transTypeID, int debtorSectorID, decimal balance)
        {
            List<recBilling> tt  = new List<recBilling>();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"
                                        select 
	                                        recBilling.DebtorSectorID
                                            , recDebtor.DebtorID,recDebtor.DebtorName
                                            , recBilling.TransTypeID
                                            , recTransactionType.TransTypeName
                                            , recBilling.Value
                                            , recBilling.Balance
                                            , recBilling.BillingDate
                                            , recBilling.DueDate 
                                        from 
	                                        transactions.recBilling
                                            inner join master.recTransactionType on recTransactionType.TransTypeID = recBilling.TransTypeID
                                            inner join master.recDebtorSector on recDebtorSector.DebtorSectorID = recBilling.DebtorSectorID
                                            inner join master.recDebtor on recDebtor.DebtorID = recDebtorSector.DebtorID
                                            where recBilling.BillingDate between @periodeAwal and periodeAkhir @periodeAkhir and
                                            recBilling.DueDate between @dueDateAwal and @dueDateAkhir and
                                            recBilling.TransTypeID = @transTypeID and
                                            recBilling.DebtorSectorID = @debtorSectorID and
                                            recBilling.Balance = @balance
                                            order by recBilling.DebtorSectorID, recBilling.BillingDate, recBilling.TransTypeID";

                    
                    cmd.CommandType = CommandType.Text;
                    
                    cmd.Parameters.Add("@periodeAwal", SqlDbType.Date).Value = periodeAwal;
                    cmd.Parameters.Add("@periodeAkhir", SqlDbType.Date).Value = periodeAkhir;
                    cmd.Parameters.Add("@dueDateAwal", SqlDbType.Date).Value = dueDateAwal;
                    cmd.Parameters.Add("@dueDateAkhir", SqlDbType.Date).Value = dueDateAkhir;
                    cmd.Parameters.Add("@transTypeID", SqlDbType.SmallInt).Value = transTypeID;
                    cmd.Parameters.Add("@debtorSectorID", SqlDbType.Int).Value = debtorSectorID;
                    cmd.Parameters.Add("@balance", SqlDbType.Money).Value = balance;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recBilling t = new recBilling(true);
                                t.DebtorSectorID = dr[recBilling.Fields.DebtorSectorID] == DBNull.Value ? 0 : Convert.ToInt32(dr[recBilling.Fields.DebtorSectorID]);
                                t.recDebtor.DebtorID = dr[recDebtor.Fields.DebtorID] == DBNull.Value ? 0 : Convert.ToInt32(dr[recDebtor.Fields.DebtorID]);
                                t.recDebtor.DebtorName = dr[recDebtor.Fields.DebtorName] == DBNull.Value ? null : dr[recDebtor.Fields.DebtorName].ToString();
                                t.TransTypeID = dr[recBilling.Fields.TransTypeID] == DBNull.Value ? (short)0 : Convert.ToInt16(dr[recBilling.Fields.TransTypeID]);
                                t.recTransactionType.TransTypeName = dr[recTransactionType.Fields.TransTypeName] == DBNull.Value ? string.Empty : dr[recTransactionType.Fields.TransTypeName].ToString();
                                t.Value = dr[recBilling.Fields.Value] == DBNull.Value ? (decimal)0 : Convert.ToDecimal(dr[recBilling.Fields.Value]);
                                t.Balance = dr[recBilling.Fields.Balance] == DBNull.Value ? (decimal)0 : Convert.ToDecimal(dr[recBilling.Fields.Balance]);
                                t.BillingDate = dr[recBilling.Fields.BillingDate] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dr[recBilling.Fields.BillingDate]);
                                t.DueDate = dr[recBilling.Fields.DueDate] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dr[recBilling.Fields.DueDate]);

                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public DataTable getDataAksiDebitur(int userID)
        {
            DataTable dt = new DataTable();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"
                                        select distinct
                                            recDebtorSector.DebtorSectorID, recDebtor.DebtorName, recSector.SectorName,recSubSector.SubSectorName, recSubSubSector.SubSubSectorName
                                        from master.recDebtor 
                                            inner join master.recDebtorSector on recDebtor.DebtorID = recDebtorSector.DebtorID
                                            inner join master.recSector on recSector.SectorID = recDebtorSector.SectorID
                                            inner join master.recSubSector on recSubSector.SubSectorID = recDebtorSector.SubSectorID
                                            inner join master.recSubSubSector on recSubSubSector.SubSubSectorID = recDebtorSector.SubSubSectorID
                                            inner join master.recUserSector on recUserSector.SubSubSectorID = recSubSubSector.SubSubSectorID
                                            inner join master.recUser on recUser.DebtorID = recDebtorSector.DebtorID
                                        where recUserSector.UserID = " + userID;

                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = cmd;

                    try
                    {

                        conn.Open();
                        da.Fill(dt);
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }

                }
            }
            return dt;
        }
    }
}
